SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[SP_TXMVTO_CNTB_Q28]
/*------------------------------------------------------------*/
/*----- Empresa          : OFISIS S.A.                    ----*/
/*----- Cliente          : OFISIS S.A.                    ----*/
/*----- Programa         : Ventas PDT                    ----*/
/*-----                    Para Terceros                  ----*/
/*----- Nombre           : SP_TXMVTO_CNTB_Q28             ----*/
/*----- Desarrollado por : Martin Garcia Farfan           ----*/
/*----- Fecha            : 21/03/2001                     ----*/
/*----- Base de Datos    : SQL Server                     ----*/
/*----- Version          : 7.0                            ----*/
/*------------------------------------------------------------*/
/*------------------------------------------------------------*/
/*----- Drop Proc SP_TXMVTO_CNTB_Q28                      ----*/
/*----- Grant all on SP_TXMVTO_CNTB_Q28 to Public         ----*/
/*----- 
SP_TXMVTO_CNTB_Q28 '01', 1, 2006, 3400,2
     ----*/
/*------------------------------------------------------------*/
@ISCO_EMPR       TD_VC_002,
@INNU_CNTB_EMPR  TD_IN_001_CONT,
@INNU_ANNO       TD_IN_004_ANNO,
@INIM_UITS       TD_NU_016_004,
@INNU_MAXI       TD_IN_001
As

Declare
 @CSCO_CLNT      TD_VC_020,
 @CSNO_CORT_CLNT TD_VC_100,
 @CSNU_RUCS      TD_VC_020,
 @CSTI_DOCU_SUNA TD_VC_003,
 @CNIM_INAF_SOLE TD_NU_016_004,
 @CNIM_AFEC_SOLE TD_NU_016_004,
 @VNIM_TOTA      TD_NU_016_004,
 @VNIM_TOPE      TD_NU_016_004,
 @VNNU_CONT      TD_IN_001,
 @VSNU_RUCS      TD_VC_020,
 @VSNU_CONT      TD_VC_010,
 @VSCO_CLNT      TD_VC_020,
 @VSNU_RUCS_TEMP TD_VC_020,
 @VSNO_CLNT_TEMP TD_VC_100,
 @VSIM_TOTA      TD_VC_020,
 @VSNU_ANNO      Varchar(04),
 @VSDE_LINE      Varchar(255),

 @VSCO_DOCU_SUNA   TD_VC_003,  
 @VSNU_DOCU_SUNA   TD_VC_020,  
 @VSAP_PATE_AUXI   TD_VC_020,
 @VSAP_MATE_AUXI   TD_VC_020,
 @VSNO_NATU_AUX1   TD_VC_020,
 @VSNO_NATU_AUX2   TD_VC_020,
 @VSNO_RAZO_SOCI   Varchar(200),

 @CSCO_DOCU_SUNA   TD_VC_003,  
 @CSNU_DOCU_SUNA   TD_VC_020,  
 @CSAP_PATE_AUXI   TD_VC_020,
 @CSAP_MATE_AUXI   TD_VC_020,
 @CSNO_NATU_AUX1   TD_VC_020,
 @CSNO_NATU_AUX2   TD_VC_020,
 @CSNO_RAZO_SOCI   Varchar(200),
 @VSTI_PERS_SUNA   Varchar(3),
 @CSTI_PERS_SUNA   Varchar(3)


 Delete TWPDTS_TERC

 Select @VSNU_RUCS = NU_RUCS
  from TMEMPR
 where CO_EMPR = @ISCO_EMPR

 Declare CU_TXMVTO_CNTB_Q28_1 Cursor For
 Select CO_CLNT, Max(NO_CORT_CLNT), Max(NU_RUCS_CLNT), TI_DOCU_SUNA, 
 Isnull(Sum(Isnull(IM_INAF_SOLE, 0)), 0),Isnull(Sum(Isnull(IM_AFEC_SOLE, 0)), 0),
 Max(CO_DOCU_SUNA),Max(NU_DOCU_SUNA),Max(AP_PATE_AUXI),Max(AP_MATE_AUXI),
 Max(NO_NATU_AUX1),Max(NO_NATU_AUX2),Max(NO_RAZO_SOCI),Max(TI_PERS_SUNA)
 From TWMVTO_Q027
 Where TI_DOCU_SUNA != '02'
 And NO_CORT_CLNT IS NOT NULL
 Group By CO_CLNT, TI_DOCU_SUNA    
 Order By CO_CLNT, TI_DOCU_SUNA

 Open CU_TXMVTO_CNTB_Q28_1
 Fetch CU_TXMVTO_CNTB_Q28_1 into @CSCO_CLNT, @CSNO_CORT_CLNT, @CSNU_RUCS, 
 @CSTI_DOCU_SUNA, @CNIM_INAF_SOLE, @CNIM_AFEC_SOLE, 
 @CSCO_DOCU_SUNA,@CSNU_DOCU_SUNA,@CSAP_PATE_AUXI,@CSAP_MATE_AUXI,
 @CSNO_NATU_AUX1,@CSNO_NATU_AUX2,@CSNO_RAZO_SOCI,@CSTI_PERS_SUNA

 Select @VSCO_CLNT = @CSCO_CLNT
 Select @VNIM_TOTA = 0
 Select @VNNU_CONT = 0
 Select @VSDE_LINE = NULL
 
 While @@Fetch_Status = 0
 Begin
   if @VSCO_CLNT != @CSCO_CLNT
    Begin
     Select @VNIM_TOPE = @INIM_UITS * @INNU_MAXI
    --- Si el monto total es mayor a tantas UITS se mostrara en el archivo
     if @VNIM_TOTA > @VNIM_TOPE
      Begin
        Select @VNNU_CONT = @VNNU_CONT + 1
        Select @VSNU_CONT = Convert(Varchar(10), @VNNU_CONT)
        Select @VSIM_TOTA = Convert(Varchar(20), Floor(@VNIM_TOTA))
        Select @VSNU_ANNO = Convert(Varchar(04), @INNU_ANNO)
/*        Select @VSDE_LINE = @VSNU_CONT + '|' + '6' + '|' + @VSNU_RUCS + '|' + 
              @VSNU_ANNO + '|' + '02' + '|' + '6' + '|' + @VSNU_RUCS_TEMP + '|' +
              @VSIM_TOTA + '|' + '|'  + '|' + '|' + '|' + @VSNO_CLNT_TEMP + '|'*/
        Select @VSDE_LINE = IsNull(Rtrim(Ltrim(@VSNU_CONT)),'') + '|' + '6' + '|' + 
              IsNull(Rtrim(Ltrim(@VSNU_RUCS)),'') + '|' + 
              IsNull(Rtrim(Ltrim(@VSNU_ANNO)),'') + '|' + 
              IsNull(Rtrim(Ltrim(@VSTI_PERS_SUNA)),'') + '|' + 
              IsNull(Rtrim(Ltrim(@VSCO_DOCU_SUNA)),'') + '|' + 
              IsNull(Rtrim(Ltrim(@VSNU_DOCU_SUNA)),'') + '|' +
              IsNull(Rtrim(Ltrim(@VSIM_TOTA)),'') + '|' + 
              IsNull(Rtrim(Ltrim(@VSAP_PATE_AUXI)),'') + '|' + 
              IsNull(Rtrim(Ltrim(@VSAP_MATE_AUXI)),'') + '|' + 
              IsNull(Rtrim(Ltrim(@VSNO_NATU_AUX1)),'') + '|' + 
              IsNull(Rtrim(Ltrim(@VSNO_NATU_AUX2)),'') +'|' + 
              IsNull(Rtrim(Ltrim(@VSNO_RAZO_SOCI)),'') + '|'

        If @VSDE_LINE IS NOT NULL        
          Insert TWPDTS_TERC(NU_CONT, CO_LINE)   
           values(@VNNU_CONT, Rtrim(Ltrim(@VSDE_LINE)))
        Else
          Select @VNNU_CONT = @VNNU_CONT - 1 
      End /*fin if @VNIM_TOTA > @VNIM_TOPE*/

      Select @VSCO_CLNT = @CSCO_CLNT
      Select @VNIM_TOTA = 0
    End /*fin if @VSCO_CLNT != @CSCO_CLNT*/
      
   if @CSTI_DOCU_SUNA != '07' and @CSTI_DOCU_SUNA != '08'
    Select @VNIM_TOTA = @VNIM_TOTA + @CNIM_INAF_SOLE + @CNIM_AFEC_SOLE
   
   if @CSTI_DOCU_SUNA = '08'
    Select @VNIM_TOTA = @VNIM_TOTA + @CNIM_INAF_SOLE + @CNIM_AFEC_SOLE
    
   if @CSTI_DOCU_SUNA = '07'
    Select @VNIM_TOTA = @VNIM_TOTA - Abs(@CNIM_INAF_SOLE) - Abs(@CNIM_AFEC_SOLE)
   
   Select @VSNU_RUCS_TEMP = Rtrim(Ltrim(@CSNU_RUCS))
   Select @VSNO_CLNT_TEMP = Rtrim(Ltrim(@CSNO_CORT_CLNT)) 
   Select @VSCO_DOCU_SUNA = Rtrim(Ltrim(@CSCO_DOCU_SUNA))
   Select @VSNU_DOCU_SUNA = Rtrim(Ltrim(@CSNU_DOCU_SUNA))
   Select @VSAP_PATE_AUXI = Rtrim(Ltrim(@CSAP_PATE_AUXI))
   Select @VSAP_MATE_AUXI = Rtrim(Ltrim(@CSAP_MATE_AUXI))
   Select @VSNO_NATU_AUX1 = Rtrim(Ltrim(@CSNO_NATU_AUX1))
   Select @VSNO_NATU_AUX2 = Rtrim(Ltrim(@CSNO_NATU_AUX2))
   Select @VSNO_RAZO_SOCI = Rtrim(Ltrim(@CSNO_RAZO_SOCI))
   Select @VSTI_PERS_SUNA = Rtrim(Ltrim(@CSTI_PERS_SUNA))

   Fetch CU_TXMVTO_CNTB_Q28_1 into @CSCO_CLNT, @CSNO_CORT_CLNT, @CSNU_RUCS, 
   @CSTI_DOCU_SUNA, @CNIM_INAF_SOLE, @CNIM_AFEC_SOLE, 
   @CSCO_DOCU_SUNA,@CSNU_DOCU_SUNA,@CSAP_PATE_AUXI,@CSAP_MATE_AUXI,
   @CSNO_NATU_AUX1,@CSNO_NATU_AUX2,@CSNO_RAZO_SOCI,@CSTI_PERS_SUNA
 End

Close CU_TXMVTO_CNTB_Q28_1
Deallocate CU_TXMVTO_CNTB_Q28_1

 Select @VNIM_TOPE = @INIM_UITS * @INNU_MAXI
  --- Si el monto total es mayor a tantas UITS se mostrara en el archivo
 If @VNIM_TOTA > @VNIM_TOPE
    Begin
     Select @VNNU_CONT = @VNNU_CONT + 1
     Select @VSNU_CONT = Convert(Varchar(04), @VNNU_CONT)
     Select @VSIM_TOTA = Convert(Varchar(20), Floor(@VNIM_TOTA))
     Select @VSNU_ANNO = Convert(Varchar(04), @INNU_ANNO)
     /*Select @VSDE_LINE = @VSNU_CONT + '|' + '6' + '|' + @VSNU_RUCS + '|' + 
            @VSNU_ANNO + '|' + '02' + '|' + '6' + '|' + @VSNU_RUCS_TEMP + '|' +
            @VSIM_TOTA + '|' + '|'  + '|' + '|' + '|' + @VSNO_CLNT_TEMP + '|'*/
        Select @VSDE_LINE = IsNull(Rtrim(Ltrim(@VSNU_CONT)),'') + '|' + '6' + '|' + 
              IsNull(Rtrim(Ltrim(@VSNU_RUCS)),'') + '|' + 
              IsNull(Rtrim(Ltrim(@VSNU_ANNO)),'') + '|' + 
              IsNull(Rtrim(Ltrim(@VSTI_PERS_SUNA)),'') + '|' + 
              IsNull(Rtrim(Ltrim(@VSCO_DOCU_SUNA)),'') + '|' + 
              IsNull(Rtrim(Ltrim(@VSNU_DOCU_SUNA)),'') + '|' +
              IsNull(Rtrim(Ltrim(@VSIM_TOTA)),'') + '|' + 
              IsNull(Rtrim(Ltrim(@VSAP_PATE_AUXI)),'') + '|' + 
              IsNull(Rtrim(Ltrim(@VSAP_MATE_AUXI)),'') + '|' + 
              IsNull(Rtrim(Ltrim(@VSNO_NATU_AUX1)),'') + '|' + 
              IsNull(Rtrim(Ltrim(@VSNO_NATU_AUX2)),'') +'|' + 
              IsNull(Rtrim(Ltrim(@VSNO_RAZO_SOCI)),'') + '|'

     If @VSDE_LINE IS NOT NULL
         Insert TWPDTS_TERC(NU_CONT, CO_LINE)   
         values(@VNNU_CONT, Rtrim(Ltrim(@VSDE_LINE)))
    End /*fin if @VNIM_TOTA > @VNIM_TOPE*/

Return
/*------------------------------ Fin ---------------------------------*/




GO
